//
// Community.CsharpSqlite.SQLiteClient.SqliteConnection.cs
//
// Represents an open connection to a Sqlite database file.
//
// Author(s): Vladimir Vukicevic  <vladimir@pobox.com>
//            Everaldo Canuto  <everaldo_canuto@yahoo.com.br>
//            Daniel Morgan <monodanmorg@yahoo.com>
//            Noah Hart <Noah.Hart@gmail.com>
//
// Copyright (C) 2002  Vladimir Vukicevic
//
// Permission is hereby granted, free of charge, to any person obtaining
// a copy of this software and associated documentation files (the
// "Software"), to deal in the Software without restriction, including
// without limitation the rights to use, copy, modify, merge, publish,
// distribute, sublicense, and/or sell copies of the Software, and to
// permit persons to whom the Software is furnished to do so, subject to
// the following conditions:
// 
// The above copyright notice and this permission notice shall be
// included in all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
//

using System;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Text;
using Community.CsharpSqlite.Entity;
#if NET_2_0
using System.Data.Common;
#endif

namespace Community.CsharpSqlite.SQLiteClient
{
#if NET_2_0
	public class SqliteConnection : DbConnection, ICloneable
#else
    public class SqliteConnection : IDbConnection, ICloneable
#endif
    {
        #region Fields

        private string conn_str;
        private string db_file;
        private int db_mode;
        private int db_version;
        private IntPtr sqlite_handle;
        private sqlite3 sqlite_handle2;
        private ConnectionState state;
        private Encoding encoding;
        private int busy_timeout;
#if NET_2_0
		bool disposed;
#endif

        #endregion

        #region Constructors and destructors

        public SqliteConnection()
        {
            db_file = null;
            db_mode = 0644;
            db_version = 3;
            state = ConnectionState.Closed;
            sqlite_handle = IntPtr.Zero;
            encoding = null;
            busy_timeout = 0;
        }

        public SqliteConnection(string connstring) : this()
        {
            ConnectionString = connstring;
        }


#if !NET_2_0
        public void Dispose()
        {
            Close();
        }
#else
		protected override void Dispose (bool disposing)
		{
			try {
				if (disposing && !disposed) {
					Close ();
					conn_str = null;
				}
			} finally {
				disposed = true;
				base.Dispose (disposing);
			}
		}
#endif

        #endregion

        #region Properties

#if NET_2_0
		override
#endif

        public string ConnectionString
        {
            get { return conn_str; }
            set { SetConnectionString(value); }
        }

#if NET_2_0
		override
#endif

        public int ConnectionTimeout
        {
            get { return 0; }
        }

#if NET_2_0
		override
#endif

        public string Database
        {
            get { return db_file; }
        }

#if NET_2_0
		override
#endif

        public ConnectionState State
        {
            get { return state; }
        }

        public Encoding Encoding
        {
            get { return encoding; }
        }

        public int Version
        {
            get { return db_version; }
        }

        public string ServerVersion
        {
            get { return Sqlite3.sqlite3_libversion(); }
        }

        internal sqlite3 Handle2
        {
            get { return sqlite_handle2; }
        }

        internal IntPtr Handle
        {
            get { return sqlite_handle; }
        }

#if NET_2_0
		public override string DataSource {
			get { return db_file; }
		}

#endif

        public int LastInsertRowId
        {
            get
            {
                //if (Version == 3)
                return (int) Sqlite3.sqlite3_last_insert_rowid(Handle2);
                //return (int)Sqlite.sqlite3_last_insert_rowid (Handle);
                //else
                //	return Sqlite.sqlite_last_insert_rowid (Handle);
            }
        }

        public int BusyTimeout
        {
            get { return busy_timeout; }
            set { busy_timeout = value < 0 ? 0 : value; }
        }

        #endregion

        #region Private Methods

        private void SetConnectionString(string connstring)
        {
            if (connstring == null)
            {
                Close();
                conn_str = null;
                return;
            }

            if (connstring != conn_str)
            {
                Close();
                conn_str = connstring;

                db_file = null;
                db_mode = 0644;

                string[] conn_pieces = connstring.Split(',');
                for (int i = 0; i < conn_pieces.Length; i++)
                {
                    string piece = conn_pieces[i].Trim();
                    if (piece.Length == 0)
                    {
                        // ignore empty elements
                        continue;
                    }
                    string[] arg_pieces = piece.Split('=');
                    if (arg_pieces.Length != 2)
                    {
                        throw new InvalidOperationException("Invalid connection string");
                    }
                    string token = arg_pieces[0].ToLower(CultureInfo.InvariantCulture).Trim();
                    string tvalue = arg_pieces[1].Trim();
                    string tvalue_lc = arg_pieces[1].ToLower(CultureInfo.InvariantCulture).Trim();
                    switch (token)
                    {
#if NET_2_0
						case "DataSource":
#endif
                        case "uri":
                            if (tvalue_lc.StartsWith("file://"))
                            {
                                db_file = tvalue.Substring(7);
                            }
                            else if (tvalue_lc.StartsWith("file:"))
                            {
                                db_file = tvalue.Substring(5);
                            }
                            else if (tvalue_lc.StartsWith("/"))
                            {
                                db_file = tvalue;
#if NET_2_0
							} else if (tvalue_lc.StartsWith ("|DataDirectory|",
											 StringComparison.InvariantCultureIgnoreCase)) {
								AppDomainSetup ads = AppDomain.CurrentDomain.SetupInformation;
								string filePath = String.Format ("App_Data{0}{1}",
												 Path.DirectorySeparatorChar,
												 tvalue_lc.Substring (15));
								
								db_file = Path.Combine (ads.ApplicationBase, filePath);
#endif
                            }
                            else
                            {
                                throw new InvalidOperationException("Invalid connection string: invalid URI");
                            }
                            break;

                        case "mode":
                            db_mode = Convert.ToInt32(tvalue);
                            break;

                        case "version":
                            db_version = Convert.ToInt32(tvalue);
                            if (db_version < 3) throw new InvalidOperationException("Minimum database version is 3");
                            break;

                        case "encoding": // only for sqlite2
                            encoding = Encoding.GetEncoding(tvalue);
                            break;

                        case "busy_timeout":
                            busy_timeout = Convert.ToInt32(tvalue);
                            break;
                    }
                }

                if (db_file == null)
                {
                    throw new InvalidOperationException("Invalid connection string: no URI");
                }
            }
        }

        #endregion

        #region Internal Methods

        internal void StartExec()
        {
            // use a mutex here
            state = ConnectionState.Executing;
        }

        internal void EndExec()
        {
            state = ConnectionState.Open;
        }

        #endregion

        #region Public Methods

        object ICloneable.Clone()
        {
            return new SqliteConnection(ConnectionString);
        }

#if NET_2_0
    // [MonoTODO ("handle IsolationLevel")]
		protected override DbTransaction BeginDbTransaction (IsolationLevel il)
#else
        public IDbTransaction BeginTransaction()
#endif
        {
            if (state != ConnectionState.Open)
                throw new InvalidOperationException("Invalid operation: The connection is closed");

            var t = new SqliteTransaction();
#if NET_2_0
			t.SetConnection (this);
#else
            t.Connection = this;
#endif
            SqliteCommand cmd = CreateCommand();
            cmd.CommandText = "BEGIN";
            cmd.ExecuteNonQuery();
            return t;
        }

#if NET_2_0
		public new DbTransaction BeginTransaction ()
			{
				return BeginDbTransaction (IsolationLevel.Unspecified);
		}

			public new DbTransaction BeginTransaction (IsolationLevel il)
				{
					return BeginDbTransaction (il);
			}
#else
        public IDbTransaction BeginTransaction(IsolationLevel il)
        {
            throw new InvalidOperationException();
        }
#endif

#if NET_2_0
		override
#endif

        public void Close()
        {
            if (state != ConnectionState.Open)
            {
                return;
            }

            state = ConnectionState.Closed;

            if (Version == 3)
                //Sqlite3.sqlite3_close()
                Sqlite3.sqlite3_close(sqlite_handle2);
            //else 
            //Sqlite.sqlite_close (sqlite_handle);
            sqlite_handle = IntPtr.Zero;
        }

#if NET_2_0
		override
#endif

        public void ChangeDatabase(string databaseName)
        {
            Close();
            db_file = databaseName;
            Open();
        }

#if !NET_2_0
        IDbCommand IDbConnection.CreateCommand()
        {
            return CreateCommand();
        }
#endif

#if NET_2_0
		protected override DbCommand CreateDbCommand ()
#else
        public SqliteCommand CreateCommand()
#endif
        {
            return new SqliteCommand(null, this);
        }

#if NET_2_0
		override
#endif

        public void Open()
        {
            if (conn_str == null)
            {
                throw new InvalidOperationException("No database specified");
            }

            if (state != ConnectionState.Closed)
            {
                return;
            }

            IntPtr errmsg = IntPtr.Zero;
            /*
			if (Version == 2){
				try {
					sqlite_handle = Sqlite.sqlite_open(db_file, db_mode, out errmsg);
					if (errmsg != IntPtr.Zero) {
						string msg = Marshal.PtrToStringAnsi (errmsg);
						Sqlite.sqliteFree (errmsg);
						throw new ApplicationException (msg);
					}
				} catch (DllNotFoundException) {
					db_version = 3;
				} catch (EntryPointNotFoundException) {
					db_version = 3;
				}
				
				if (busy_timeout != 0)
					Sqlite.sqlite_busy_timeout (sqlite_handle, busy_timeout);
			}
             */
            if (Version == 3)
            {
                sqlite_handle = (IntPtr) 1;
                int err = Sqlite3.sqlite3_open(db_file, ref sqlite_handle2);
                //int err = Sqlite.sqlite3_open16(db_file, out sqlite_handle);
                if (err == (int) SqliteError.ERROR)
                    throw new ApplicationException(Sqlite3.sqlite3_errmsg(sqlite_handle2));
                //throw new ApplicationException (Marshal.PtrToStringUni( Sqlite.sqlite3_errmsg16 (sqlite_handle)));
                if (busy_timeout != 0)
                    Sqlite3.sqlite3_busy_timeout(sqlite_handle2, busy_timeout);
                //Sqlite.sqlite3_busy_timeout (sqlite_handle, busy_timeout);
            }
            else
            {
            }
            state = ConnectionState.Open;
        }

        public DataTable GetSchema(String collectionName)
        {
            return GetSchema(collectionName, null);
        }

        public DataTable GetSchema(String collectionName, string[] restrictionValues)
        {
            if (State != ConnectionState.Open)
                throw new InvalidOperationException("Invalid operation.  The connection is closed.");

            int restrictionsCount = 0;
            if (restrictionValues != null)
                restrictionsCount = restrictionValues.Length;

            DataTable metaTable = GetSchemaMetaDataCollections();
            foreach (DataRow row in metaTable.Rows)
            {
                if (String.Compare(row["CollectionName"].ToString(), collectionName, true) == 0)
                {
                    var restrictions = (int) row["NumberOfRestrictions"];
                    if (restrictionsCount > restrictions)
                        throw new ArgumentException("More restrictions were provided than needed.");
                }
            }

            switch (collectionName.ToUpper())
            {
                case "METADATACOLLECTIONS":
                    return metaTable;
                case "DATASOURCEINFORMATION":
                    return GetSchemaDataSourceInformation();
                case "DATATYPES":
                    return GetSchemaDataTypes();
                case "RESTRICTIONS":
                    return GetSchemaRestrictions();
                case "RESERVEDWORDS":
                    return GetSchemaReservedWords();
                case "TABLES":
                    return GetSchemaTables(restrictionValues);
                case "COLUMNS":
                    return GetSchemaColumns(restrictionValues);
                case "VIEWS":
                    return GetSchemaViews(restrictionValues);
                case "INDEXCOLUMNS":
                    return GetSchemaIndexColumns(restrictionValues);
                case "INDEXES":
                    return GetSchemaIndexes(restrictionValues);
                case "UNIQUEKEYS":
                    throw new NotImplementedException(collectionName);
                case "PRIMARYKEYS":
                    throw new NotImplementedException(collectionName);
                case "FOREIGNKEYS":
                    return GetSchemaForeignKeys(restrictionValues);
                case "FOREIGNKEYCOLUMNS":
                    throw new NotImplementedException(collectionName);
                case "TRIGGERS":
                    return GetSchemaTriggers(restrictionValues);
            }

            throw new ArgumentException("The requested collection is not defined.");
        }

        private static readonly DataTable metaDataCollections;

        private DataTable GetSchemaMetaDataCollections()
        {
            if (metaDataCollections != null)
                return metaDataCollections;

            var dt = new DataTable();

            dt.Columns.Add("CollectionName", typeof (String));
            dt.Columns.Add("NumberOfRestrictions", typeof (Int32));
            dt.Columns.Add("NumberOfIdentifierParts", typeof (Int32));

            dt.LoadDataRow(new object[] {"MetaDataCollections", 0, 0}, true);
            dt.LoadDataRow(new object[] {"DataSourceInformation", 0, 0}, true);
            dt.LoadDataRow(new object[] {"DataTypes", 0, 0}, true);
            dt.LoadDataRow(new object[] {"Restrictions", 0, 0}, true);
            dt.LoadDataRow(new object[] {"ReservedWords", 0, 0}, true);
            dt.LoadDataRow(new object[] {"Tables", 1, 1}, true);
            dt.LoadDataRow(new object[] {"Columns", 1, 1}, true);
            dt.LoadDataRow(new object[] {"Views", 1, 1}, true);
            dt.LoadDataRow(new object[] {"IndexColumns", 1, 1}, true);
            dt.LoadDataRow(new object[] {"Indexes", 1, 1}, true);
            //dt.LoadDataRow(new object[] { "UniqueKeys", 1, 1 }, true);
            //dt.LoadDataRow(new object[] { "PrimaryKeys", 1, 1 }, true);
            dt.LoadDataRow(new object[] {"ForeignKeys", 1, 1}, true);
            //dt.LoadDataRow(new object[] { "ForeignKeyColumns", 1, 1 }, true);
            dt.LoadDataRow(new object[] {"Triggers", 1, 1}, true);

            return dt;
        }

        private DataTable GetSchemaRestrictions()
        {
            var dt = new DataTable();

            dt.Columns.Add("CollectionName", typeof (String));
            dt.Columns.Add("RestrictionName", typeof (String));
            dt.Columns.Add("ParameterName", typeof (String));
            dt.Columns.Add("RestrictionDefault", typeof (String));
            dt.Columns.Add("RestrictionNumber", typeof (Int32));

            dt.LoadDataRow(new object[] {"Tables", "Table", "TABLENAME", "TABLE_NAME", 1}, true);
            dt.LoadDataRow(new object[] {"Columns", "Table", "TABLENAME", "TABLE_NAME", 1}, true);
            dt.LoadDataRow(new object[] {"Views", "View", "VIEWNAME", "VIEW_NAME", 1}, true);
            dt.LoadDataRow(new object[] {"IndexColumns", "Name", "NAME", "INDEX_NAME", 1}, true);
            dt.LoadDataRow(new object[] {"Indexes", "TableName", "TABLENAME", "TABLE_NAME", 1}, true);
            dt.LoadDataRow(new object[] {"ForeignKeys", "Foreign_Key_Table_Name", "TABLENAME", "TABLE_NAME", 1}, true);
            dt.LoadDataRow(new object[] {"Triggers", "TableName", "TABLENAME", "TABLE_NAME", 1}, true);

            return dt;
        }

        private DataTable GetSchemaTables(string[] restrictionValues)
        {
            SqliteCommand cmd = CreateCommand();
            cmd = new SqliteCommand("SELECT type, name, tbl_name, rootpage, sql " +
                                    " FROM sqlite_master " +
                                    " WHERE (name = :pname or (:pname is null)) " +
                                    " AND type = 'table' " +
                                    " ORDER BY name", this);
            cmd.Parameters.Add("pname", DbType.String).Value = DBNull.Value;
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        private DataTable GetSchemaColumns(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException("Columns must contain at least one restriction value for the table name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = CreateCommand();
            cmd.CommandText = string.Format("PRAGMA table_info({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        private DataTable GetSchemaTriggers(string[] restrictionValues)
        {
            SqliteCommand cmd = CreateCommand();
            cmd = new SqliteCommand("SELECT type, name, tbl_name, rootpage, sql " +
                                    " FROM sqlite_master " +
                                    " WHERE (tbl_name = :pname or :pname is null) " +
                                    " AND type = 'trigger' " +
                                    " ORDER BY name", this);
            cmd.Parameters.Add("pname", DbType.String).Value = DBNull.Value;
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        private DataTable GetSchemaIndexColumns(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException(
                    "IndexColumns must contain at least one restriction value for the index name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = CreateCommand();
            cmd.CommandText = string.Format("PRAGMA index_info({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        private DataTable GetSchemaIndexes(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException("Indexes must contain at least one restriction value for the table name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = CreateCommand();
            cmd.CommandText = string.Format("PRAGMA index_list({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        private DataTable GetSchemaForeignKeys(string[] restrictionValues)
        {
            if (restrictionValues == null || restrictionValues.Length == 0)
            {
                throw new ArgumentException(
                    "Foreign Keys must contain at least one restriction value for the table name.");
            }
            ValidateIdentifier(restrictionValues[0]);

            SqliteCommand cmd = CreateCommand();
            cmd.CommandText = string.Format("PRAGMA foreign_key_list({0})", restrictionValues[0]);
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        private void ValidateIdentifier(string value)
        {
            if (value.Contains("'"))
                throw new ArgumentException("Identifiers can not contain a single quote.");
        }

        private DataTable GetSchemaViews(string[] restrictionValues)
        {
            SqliteCommand cmd = CreateCommand();
            cmd = new SqliteCommand("SELECT type, name, tbl_name, rootpage, sql " +
                                    " FROM sqlite_master " +
                                    " WHERE (name = :pname or :pname is null) " +
                                    " AND type = 'view' " +
                                    " ORDER BY name", this);
            cmd.Parameters.Add("pname", DbType.String).Value = DBNull.Value;
            return GetSchemaDataTable(cmd, restrictionValues);
        }

        private DataTable GetSchemaDataSourceInformation()
        {
            var dt = new DataTable();

            dt.Columns.Add("CompositeIdentifierSeparatorPattern", typeof (String));
            dt.Columns.Add("DataSourceProductName", typeof (String));
            dt.Columns.Add("DataSourceProductVersion", typeof (String));
            dt.Columns.Add("DataSourceProductVersionNormalized", typeof (String));
            dt.Columns.Add("GroupByBehavior", typeof (GroupByBehavior));
            dt.Columns.Add("IdentifierPattern", typeof (String));
            dt.Columns.Add("IdentifierCase", typeof (IdentifierCase));
            dt.Columns.Add("OrderByColumnsInSelect", typeof (Boolean));
            dt.Columns.Add("ParameterMarkerFormat", typeof (String));
            dt.Columns.Add("ParameterMarkerPattern", typeof (String));
            dt.Columns.Add("ParameterNameMaxLength", typeof (Int32));
            dt.Columns.Add("ParameterNamePattern", typeof (String));
            dt.Columns.Add("QuotedIdentifierPattern", typeof (String));
            dt.Columns.Add("QuotedIdentifierCase", typeof (IdentifierCase));
            dt.Columns.Add("StatementSeparatorPattern", typeof (String));
            dt.Columns.Add("StringLiteralPattern", typeof (String));
            dt.Columns.Add("SupportedJoinOperators", typeof (SupportedJoinOperators));

            // TODO: set correctly
            dt.LoadDataRow(new object[]
                               {
                                   "",
                                   "SQLite",
                                   ServerVersion,
                                   ServerVersion,
                                   3,
                                   "",
                                   1,
                                   false,
                                   "",
                                   "",
                                   30,
                                   "",
                                   2,
                                   DBNull.Value,
                                   ""
                               },
                           true);

            return dt;
        }

        private DataTable GetSchemaDataTypes()
        {
            var dt = new DataTable();

            dt.Columns.Add("TypeName", typeof (String));
            dt.Columns.Add("ProviderDbType", typeof (String));
            dt.Columns.Add("StorageType", typeof (Int32));
            dt.Columns.Add("DataType", typeof (String));
            // TODO: fill the rest of these
            /*
      dt.Columns.Add("ColumnSize", typeof(System.Int64));
      dt.Columns.Add("CreateFormat", typeof(System.String));
      dt.Columns.Add("CreateParameters", typeof(System.String));
      dt.Columns.Add("IsAutoIncrementable",typeof(System.Boolean));
      dt.Columns.Add("IsBestMatch", typeof(System.Boolean));
      dt.Columns.Add("IsCaseSensitive", typeof(System.Boolean));
      dt.Columns.Add("IsFixedLength", typeof(System.Boolean));
      dt.Columns.Add("IsFixedPrecisionScale",typeof(System.Boolean));
      dt.Columns.Add("IsLong", typeof(System.Boolean));
      dt.Columns.Add("IsNullable", typeof(System.Boolean));
      dt.Columns.Add("IsSearchable", typeof(System.Boolean));
      dt.Columns.Add("IsSearchableWithLike",typeof(System.Boolean));
      dt.Columns.Add("IsUnsigned", typeof(System.Boolean));
      dt.Columns.Add("MaximumScale", typeof(System.Int16));
      dt.Columns.Add("MinimumScale", typeof(System.Int16));
      dt.Columns.Add("IsConcurrencyType",typeof(System.Boolean));
      dt.Columns.Add("IsLiteralSupported",typeof(System.Boolean));
      dt.Columns.Add("LiteralPrefix", typeof(System.String));
      dt.Columns.Add("LiteralSuffix", typeof(System.String));
      */

            dt.LoadDataRow(new object[] {"INT", "INTEGER", 1, "System.Int32"}, true);
            dt.LoadDataRow(new object[] {"INTEGER", "INTEGER", 1, "System.Int32"}, true);
            dt.LoadDataRow(new object[] {"TINYINT", "INTEGER", 1, "System.Byte"}, true);
            dt.LoadDataRow(new object[] {"SMALLINT", "INTEGER", 1, "System.Int16"}, true);
            dt.LoadDataRow(new object[] {"MEDIUMINT", "INTEGER", 1, "System.Int32"}, true);
            dt.LoadDataRow(new object[] {"BIGINT", "INTEGER", 1, "System.Int64"}, true);
            dt.LoadDataRow(new object[] {"UNSIGNED BIGINT", "INTEGER", 1, "System.UInt64"}, true);
            dt.LoadDataRow(new object[] {"INT2", "INTEGER", 1, "System.Int16"}, true);
            dt.LoadDataRow(new object[] {"INT8", "INTEGER", 1, "System.Int64"}, true);

            dt.LoadDataRow(new object[] {"CHARACTER", "TEXT", 2, "System.String"}, true);
            dt.LoadDataRow(new object[] {"VARCHAR", "TEXT", 2, "System.String"}, true);
            dt.LoadDataRow(new object[] {"VARYING CHARACTER", "TEXT", 2, "System.String"}, true);
            dt.LoadDataRow(new object[] {"NCHAR", "TEXT", 2, "System.String"}, true);
            dt.LoadDataRow(new object[] {"NATIVE CHARACTER", "TEXT", 2, "System.String"}, true);
            dt.LoadDataRow(new object[] {"NVARHCAR", "TEXT", 2, "System.String"}, true);
            dt.LoadDataRow(new object[] {"TEXT", "TEXT", 2, "System.String"}, true);
            dt.LoadDataRow(new object[] {"CLOB", "TEXT", 2, "System.String"}, true);

            dt.LoadDataRow(new object[] {"BLOB", "NONE", 3, "System.Byte[]"}, true);

            dt.LoadDataRow(new object[] {"REAL", "REAL", 4, "System.Double"}, true);
            dt.LoadDataRow(new object[] {"DOUBLE", "REAL", 4, "System.Double"}, true);
            dt.LoadDataRow(new object[] {"DOUBLE PRECISION", "REAL", 4, "System.Double"}, true);
            dt.LoadDataRow(new object[] {"FLOAT", "REAL", 4, "System.Double"}, true);

            dt.LoadDataRow(new object[] {"NUMERIC", "NUMERIC", 5, "System.Decimal"}, true);
            dt.LoadDataRow(new object[] {"DECIMAL", "NUMERIC", 5, "System.Decimal"}, true);
            dt.LoadDataRow(new object[] {"BOOLEAN", "NUMERIC", 5, "System.Boolean"}, true);
            dt.LoadDataRow(new object[] {"DATE", "NUMERIC", 5, "System.DateTime"}, true);
            dt.LoadDataRow(new object[] {"DATETIME", "NUMERIC", 5, "System.DateTime"}, true);

            return dt;
        }

        private DataTable GetSchemaReservedWords()
        {
            var dt = new DataTable();

            dt.Columns.Add("ReservedWord", typeof (String));

            dt.LoadDataRow(new object[] {"ABORT"}, true);
            dt.LoadDataRow(new object[] {"ACTION"}, true);
            dt.LoadDataRow(new object[] {"ADD"}, true);
            dt.LoadDataRow(new object[] {"AFTER"}, true);
            dt.LoadDataRow(new object[] {"ALL"}, true);
            dt.LoadDataRow(new object[] {"ANALYZE"}, true);
            dt.LoadDataRow(new object[] {"AND"}, true);
            dt.LoadDataRow(new object[] {"AS"}, true);
            dt.LoadDataRow(new object[] {"ATTACH"}, true);
            dt.LoadDataRow(new object[] {"AUTOINCREMENT"}, true);
            dt.LoadDataRow(new object[] {"BEFORE"}, true);
            dt.LoadDataRow(new object[] {"BEFORE"}, true);
            dt.LoadDataRow(new object[] {"BEGIN"}, true);
            dt.LoadDataRow(new object[] {"BETWEEN"}, true);
            dt.LoadDataRow(new object[] {"BY"}, true);
            dt.LoadDataRow(new object[] {"CASCADE"}, true);
            dt.LoadDataRow(new object[] {"CASE"}, true);
            dt.LoadDataRow(new object[] {"CAST"}, true);
            dt.LoadDataRow(new object[] {"CHECK"}, true);
            dt.LoadDataRow(new object[] {"COLLATE"}, true);
            dt.LoadDataRow(new object[] {"COLUMN"}, true);
            dt.LoadDataRow(new object[] {"COMMIT"}, true);
            dt.LoadDataRow(new object[] {"CONFLICT"}, true);
            dt.LoadDataRow(new object[] {"CONTRAINT"}, true);
            dt.LoadDataRow(new object[] {"CREATE"}, true);
            dt.LoadDataRow(new object[] {"CROSS"}, true);
            dt.LoadDataRow(new object[] {"CURRENT_DATE"}, true);
            dt.LoadDataRow(new object[] {"CURRENT_TIME"}, true);
            dt.LoadDataRow(new object[] {"CURRENT_TIMESTAMP"}, true);
            dt.LoadDataRow(new object[] {"DATABASE"}, true);
            dt.LoadDataRow(new object[] {"DEFAULT"}, true);
            dt.LoadDataRow(new object[] {"DEFERRABLE"}, true);
            dt.LoadDataRow(new object[] {"DEFERRED"}, true);
            dt.LoadDataRow(new object[] {"DELETE"}, true);
            dt.LoadDataRow(new object[] {"DESC"}, true);
            dt.LoadDataRow(new object[] {"DETACH"}, true);
            dt.LoadDataRow(new object[] {"DISTINCT"}, true);
            dt.LoadDataRow(new object[] {"DROP"}, true);
            dt.LoadDataRow(new object[] {"EACH"}, true);
            dt.LoadDataRow(new object[] {"ELSE"}, true);
            dt.LoadDataRow(new object[] {"END"}, true);
            dt.LoadDataRow(new object[] {"ESCAPE"}, true);
            dt.LoadDataRow(new object[] {"EXCEPT"}, true);
            dt.LoadDataRow(new object[] {"EXCLUSIVE"}, true);
            dt.LoadDataRow(new object[] {"EXISTS"}, true);
            dt.LoadDataRow(new object[] {"EXPLAIN"}, true);
            dt.LoadDataRow(new object[] {"FAIL"}, true);
            dt.LoadDataRow(new object[] {"FOR"}, true);
            dt.LoadDataRow(new object[] {"FOREIGN"}, true);
            dt.LoadDataRow(new object[] {"FROM"}, true);
            dt.LoadDataRow(new object[] {"FULL"}, true);
            dt.LoadDataRow(new object[] {"GLOB"}, true);
            dt.LoadDataRow(new object[] {"GROUP"}, true);
            dt.LoadDataRow(new object[] {"HAVING"}, true);
            dt.LoadDataRow(new object[] {"IF"}, true);
            dt.LoadDataRow(new object[] {"IGNORE"}, true);
            dt.LoadDataRow(new object[] {"IMMEDIATE"}, true);
            dt.LoadDataRow(new object[] {"IN"}, true);
            dt.LoadDataRow(new object[] {"INDEX"}, true);
            dt.LoadDataRow(new object[] {"INITIALLY"}, true);
            dt.LoadDataRow(new object[] {"INNER"}, true);
            dt.LoadDataRow(new object[] {"INSERT"}, true);
            dt.LoadDataRow(new object[] {"INSTEAD"}, true);
            dt.LoadDataRow(new object[] {"INTERSECT"}, true);
            dt.LoadDataRow(new object[] {"INTO"}, true);
            dt.LoadDataRow(new object[] {"IS"}, true);
            dt.LoadDataRow(new object[] {"ISNULL"}, true);
            dt.LoadDataRow(new object[] {"JOIN"}, true);
            dt.LoadDataRow(new object[] {"KEY"}, true);
            dt.LoadDataRow(new object[] {"LEFT"}, true);
            dt.LoadDataRow(new object[] {"LIKE"}, true);
            dt.LoadDataRow(new object[] {"LIMIT"}, true);
            dt.LoadDataRow(new object[] {"MATCH"}, true);
            dt.LoadDataRow(new object[] {"NATURAL"}, true);
            dt.LoadDataRow(new object[] {"NO"}, true);
            dt.LoadDataRow(new object[] {"NOT"}, true);
            dt.LoadDataRow(new object[] {"NOT NULL"}, true);
            dt.LoadDataRow(new object[] {"OF"}, true);
            dt.LoadDataRow(new object[] {"OFFSET"}, true);
            dt.LoadDataRow(new object[] {"ON"}, true);
            dt.LoadDataRow(new object[] {"OR"}, true);
            dt.LoadDataRow(new object[] {"ORDER"}, true);
            dt.LoadDataRow(new object[] {"OUTER"}, true);
            dt.LoadDataRow(new object[] {"PLAN"}, true);
            dt.LoadDataRow(new object[] {"PRAGMA"}, true);
            dt.LoadDataRow(new object[] {"PRIMARY"}, true);
            dt.LoadDataRow(new object[] {"QUERY"}, true);
            dt.LoadDataRow(new object[] {"RAISE"}, true);
            dt.LoadDataRow(new object[] {"REFERENCES"}, true);
            dt.LoadDataRow(new object[] {"REGEXP"}, true);
            dt.LoadDataRow(new object[] {"REINDEX"}, true);
            dt.LoadDataRow(new object[] {"RELEASE"}, true);
            dt.LoadDataRow(new object[] {"RENAME"}, true);
            dt.LoadDataRow(new object[] {"REPLACE"}, true);
            dt.LoadDataRow(new object[] {"RESTRICT"}, true);
            dt.LoadDataRow(new object[] {"RIGHT"}, true);
            dt.LoadDataRow(new object[] {"ROLLBACK"}, true);
            dt.LoadDataRow(new object[] {"ROW"}, true);
            dt.LoadDataRow(new object[] {"SAVEPOOINT"}, true);
            dt.LoadDataRow(new object[] {"SELECT"}, true);
            dt.LoadDataRow(new object[] {"SET"}, true);
            dt.LoadDataRow(new object[] {"TABLE"}, true);
            dt.LoadDataRow(new object[] {"TEMP"}, true);
            dt.LoadDataRow(new object[] {"TEMPORARY"}, true);
            dt.LoadDataRow(new object[] {"THEN"}, true);
            dt.LoadDataRow(new object[] {"TO"}, true);
            dt.LoadDataRow(new object[] {"TRANSACTION"}, true);
            dt.LoadDataRow(new object[] {"TRIGGER"}, true);
            dt.LoadDataRow(new object[] {"UNION"}, true);
            dt.LoadDataRow(new object[] {"UNIQUE"}, true);
            dt.LoadDataRow(new object[] {"UPDATE"}, true);
            dt.LoadDataRow(new object[] {"USING"}, true);
            dt.LoadDataRow(new object[] {"VACUUM"}, true);
            dt.LoadDataRow(new object[] {"VALUES"}, true);
            dt.LoadDataRow(new object[] {"VIEW"}, true);
            dt.LoadDataRow(new object[] {"VIRTUAL"}, true);
            dt.LoadDataRow(new object[] {"WHEN"}, true);
            dt.LoadDataRow(new object[] {"WHERE"}, true);

            return dt;
        }

        private DataTable GetSchemaDataTable(SqliteCommand cmd, string[] restrictionValues)
        {
            if (restrictionValues != null && cmd.Parameters.Count > 0)
            {
                for (int i = 0; i < restrictionValues.Length; i++)
                    cmd.Parameters[i].Value = restrictionValues[i];
            }

            var adapter = new SqliteDataAdapter(cmd);
            var dt = new DataTable();
            adapter.Fill(dt);

            return dt;
        }

        #endregion
    }
}